import java.sql.*;

/**
 * JDBC Basics Examples
 * This class demonstrates the fundamental concepts of JDBC including:
 * - Connection establishment
 * - Statement and PreparedStatement usage
 * - ResultSet handling
 * - Basic CRUD operations
 */
public class JDBCBasics {
    
    // Database connection parameters
    private static final String URL = "jdbc:mysql://localhost:3306/testdb";
    private static final String USER = "root";
    private static final String PASSWORD = "password";
    
    public static void main(String[] args) {
        JDBCBasics example = new JDBCBasics();
        
        // Demonstrating JDBC connection
        example.demonstrateConnection();
        
        // Demonstrating Statement usage
        example.demonstrateStatement();
        
        // Demonstrating PreparedStatement usage
        example.demonstratePreparedStatement();
        
        // Demonstrating ResultSet usage
        example.demonstrateResultSet();
        
        // Demonstrating CRUD operations
        example.demonstrateCRUDOperations();
    }
    
    /**
     * Method to demonstrate JDBC connection establishment
     */
    public void demonstrateConnection() {
        System.out.println("=== JDBC Connection Example ===");
        
        Connection connection = null;
        try {
            // Loading the JDBC driver (optional in newer versions)
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("MySQL JDBC Driver registered successfully");
            
            // Establishing the connection
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println("Connected to database successfully");
            
            // Displaying connection metadata
            DatabaseMetaData metaData = connection.getMetaData();
            System.out.println("Database Product Name: " + metaData.getDatabaseProductName());
            System.out.println("Database Product Version: " + metaData.getDatabaseProductVersion());
            System.out.println("Driver Name: " + metaData.getDriverName());
            System.out.println("Driver Version: " + metaData.getDriverVersion());
            
        } catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC Driver not found");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Connection failed");
            e.printStackTrace();
        } finally {
            // Closing the connection
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("Connection closed");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        
        System.out.println();
    }
    
    /**
     * Method to demonstrate Statement interface usage
     */
    public void demonstrateStatement() {
        System.out.println("=== Statement Interface Example ===");
        
        Connection connection = null;
        Statement statement = null;
        
        try {
            // Establishing connection
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            
            // Creating Statement object
            statement = connection.createStatement();
            
            // Executing DDL statement (CREATE TABLE)
            String createTableSQL = "CREATE TABLE IF NOT EXISTS employees (" +
                                   "id INT PRIMARY KEY AUTO_INCREMENT, " +
                                   "name VARCHAR(100), " +
                                   "age INT, " +
                                   "salary DOUBLE)";
            boolean isCreated = statement.execute(createTableSQL);
            System.out.println("Table creation result: " + isCreated);
            
            // Executing DML statement (INSERT)
            String insertSQL = "INSERT INTO employees (name, age, salary) VALUES " +
                              "('John Doe', 30, 50000.0), " +
                              "('Jane Smith', 25, 60000.0)";
            int rowsAffected = statement.executeUpdate(insertSQL);
            System.out.println("Rows inserted: " + rowsAffected);
            
            // Executing SELECT statement
            String selectSQL = "SELECT * FROM employees";
            ResultSet resultSet = statement.executeQuery(selectSQL);
            
            // Processing the ResultSet
            System.out.println("Employee Data:");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                double salary = resultSet.getDouble("salary");
                System.out.println("ID: " + id + ", Name: " + name + 
                                 ", Age: " + age + ", Salary: " + salary);
            }
            
            // Closing ResultSet
            resultSet.close();
            
        } catch (SQLException e) {
            System.out.println("Statement execution failed");
            e.printStackTrace();
        } finally {
            // Closing resources
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        System.out.println();
    }
    
    /**
     * Method to demonstrate PreparedStatement interface usage
     */
    public void demonstratePreparedStatement() {
        System.out.println("=== PreparedStatement Interface Example ===");
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            // Establishing connection
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            
            // Using PreparedStatement to insert data
            String insertSQL = "INSERT INTO employees (name, age, salary) VALUES (?, ?, ?)";
            preparedStatement = connection.prepareStatement(insertSQL);
            
            // Setting parameters
            preparedStatement.setString(1, "Bob Johnson");
            preparedStatement.setInt(2, 35);
            preparedStatement.setDouble(3, 55000.0);
            
            // Executing the statement
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println("Rows inserted with PreparedStatement: " + rowsAffected);
            
            // Using PreparedStatement with parameters for SELECT
            String selectSQL = "SELECT * FROM employees WHERE age > ?";
            PreparedStatement selectStatement = connection.prepareStatement(selectSQL);
            selectStatement.setInt(1, 25);
            
            ResultSet resultSet = selectStatement.executeQuery();
            System.out.println("Employees older than 25:");
            while (resultSet.next()) {
                System.out.println("Name: " + resultSet.getString("name") + 
                                 ", Age: " + resultSet.getInt("age") + 
                                 ", Salary: " + resultSet.getDouble("salary"));
            }
            
            // Closing resources
            resultSet.close();
            selectStatement.close();
            
        } catch (SQLException e) {
            System.out.println("PreparedStatement execution failed");
            e.printStackTrace();
        } finally {
            // Closing resources
            try {
                if (preparedStatement != null) preparedStatement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        System.out.println();
    }
    
    /**
     * Method to demonstrate ResultSet interface usage
     */
    public void demonstrateResultSet() {
        System.out.println("=== ResultSet Interface Example ===");
        
        Connection connection = null;
        Statement statement = null;
        
        try {
            // Establishing connection
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            statement = connection.createStatement();
            
            // Executing query
            String selectSQL = "SELECT * FROM employees ORDER BY salary DESC";
            ResultSet resultSet = statement.executeQuery(selectSQL);
            
            // Getting ResultSet metadata
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            System.out.println("Column Count: " + columnCount);
            
            // Printing column names
            System.out.print("Columns: ");
            for (int i = 1; i <= columnCount; i++) {
                System.out.print(metaData.getColumnName(i) + "\t");
            }
            System.out.println();
            
            // Moving through ResultSet in different ways
            System.out.println("Moving through ResultSet:");
            
            // Moving to first row
            if (resultSet.first()) {
                System.out.println("First row: " + resultSet.getString("name") + 
                                 " - Salary: " + resultSet.getDouble("salary"));
            }
            
            // Moving to last row
            if (resultSet.last()) {
                System.out.println("Last row: " + resultSet.getString("name") + 
                                 " - Salary: " + resultSet.getDouble("salary"));
            }
            
            // Moving to absolute position
            if (resultSet.absolute(2)) {
                System.out.println("Second row: " + resultSet.getString("name") + 
                                 " - Salary: " + resultSet.getDouble("salary"));
            }
            
            // Moving relative to current position
            resultSet.relative(-1); // Move one row back
            System.out.println("Previous row: " + resultSet.getString("name") + 
                             " - Salary: " + resultSet.getDouble("salary"));
            
            // Closing ResultSet
            resultSet.close();
            
        } catch (SQLException e) {
            System.out.println("ResultSet demonstration failed");
            e.printStackTrace();
        } finally {
            // Closing resources
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        System.out.println();
    }
    
    /**
     * Method to demonstrate CRUD operations using JDBC
     */
    public void demonstrateCRUDOperations() {
        System.out.println("=== CRUD Operations Example ===");
        
        Connection connection = null;
        
        try {
            // Establishing connection
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
            
            // CREATE - Insert new employee
            createEmployee(connection, "Alice Wilson", 28, 65000.0);
            
            // READ - Retrieve all employees
            System.out.println("All employees after CREATE:");
            readAllEmployees(connection);
            
            // UPDATE - Update employee salary
            updateEmployeeSalary(connection, "Alice Wilson", 70000.0);
            System.out.println("Employees after UPDATE:");
            readAllEmployees(connection);
            
            // DELETE - Delete employee
            deleteEmployee(connection, "Alice Wilson");
            System.out.println("Employees after DELETE:");
            readAllEmployees(connection);
            
        } catch (SQLException e) {
            System.out.println("CRUD operations failed");
            e.printStackTrace();
        } finally {
            // Closing resources
            try {
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        System.out.println();
    }
    
    /**
     * Helper method to create (insert) an employee
     */
    private void createEmployee(Connection connection, String name, int age, double salary) 
            throws SQLException {
        String insertSQL = "INSERT INTO employees (name, age, salary) VALUES (?, ?, ?)";
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, age);
            preparedStatement.setDouble(3, salary);
            
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println("Employee created: " + name + " (Rows affected: " + rowsAffected + ")");
        }
    }
    
    /**
     * Helper method to read (select) all employees
     */
    private void readAllEmployees(Connection connection) throws SQLException {
        String selectSQL = "SELECT * FROM employees";
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSQL)) {
            
            while (resultSet.next()) {
                System.out.println("ID: " + resultSet.getInt("id") + 
                                 ", Name: " + resultSet.getString("name") + 
                                 ", Age: " + resultSet.getInt("age") + 
                                 ", Salary: " + resultSet.getDouble("salary"));
            }
        }
    }
    
    /**
     * Helper method to update an employee's salary
     */
    private void updateEmployeeSalary(Connection connection, String name, double newSalary) 
            throws SQLException {
        String updateSQL = "UPDATE employees SET salary = ? WHERE name = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
            preparedStatement.setDouble(1, newSalary);
            preparedStatement.setString(2, name);
            
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println("Employee salary updated for: " + name + " (Rows affected: " + rowsAffected + ")");
        }
    }
    
    /**
     * Helper method to delete an employee
     */
    private void deleteEmployee(Connection connection, String name) throws SQLException {
        String deleteSQL = "DELETE FROM employees WHERE name = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
            preparedStatement.setString(1, name);
            
            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println("Employee deleted: " + name + " (Rows affected: " + rowsAffected + ")");
        }
    }
}